var conf = require('../conf/conf.js');
var modeles = {
	TABLE_ARTICLE:`CREATE TABLE ${conf.mysql.tables.TABLE_ARTICLE} 
	(
		id INTEGER UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
		featureID INTEGER UNSIGNED NOT NULL,
		title VARCHAR(128) NOT NULL,
		subtitle VARCHAR(128),
		link VARCHAR(256) NOT NULL,
		license VARCHAR(256),
		timeCreated TIMESTAMP NOT NULL 	DEFAULT CURRENT_TIMESTAMP,
		timeRelease TIMESTAMP NOT NULL,
		timeModify TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
		author VARCHAR(128) NOT NULL,
		introduction VARCHAR(256),
		coverLink VARCHAR(256),
		content TEXT NOT NULL,
		countRead INTEGER UNSIGNED NOT NULL DEFAULT 0,
		countShare INTEGER UNSIGNED NOT NULL DEFAULT 0,
		countDiscuss INTEGER UNSIGNED NOT NULL DEFAULT 0,
		labels VARCHAR(256),
		state VARCHAR(16) NOT NULL DEFAULT  'pass'
	)`,
	TABLE_FEATURE:`CREATE TABLE ${conf.mysql.tables.TABLE_FEATURE}
	(
		id INTEGER UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
		title VARCHAR(20) NOT NULL,
		timeCreate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
		author VARCHAR(128) NOT NULL,
		coverLink VARCHAR(256),
		introduction VARCHAR(256),
		countArticle INTEGER UNSIGNED NOT NULL DEFAULT 0
	)`,
	TABLE_USER:`CREATE TABLE ${conf.mysql.tables.TABLE_USER}
	(
		name VARCHAR(128) NOT NULL PRIMARY KEY,
		nickname VARCHAR(128) NOT NULL,
		password VARCHAR(512) NOT NULL,
		question VARCHAR(128) NOT NULL,
		anser VARCHAR(128) NOT NULL,
		authority VARCHAR(16) NOT NULL DEFAULT 'website',
		timeCreate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
		state VARCHAR(16) NOT NULL DEFAULT 'pass'
	)`,
	TABLE_DISCUSS:`CREATE TABLE ${conf.mysql.tables.TABLE_DISCUSS}
	(
		id INTEGER UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
		author VARCHAR(32) NOT NULL,
		contact VARCHAR(64),
		discussID INTEGER UNSIGNED NOT NULL,
		timeCreate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
		content VARCHAR(256) NOT NULL,
		state VARCHAR(16) NOT NULL DEFAULT  'verify',
		mask VARCHAR(16) NOT NULL DEFAULT 'comman',
		type VARCHAR(16) NOT NULL DEFAULT 'disc'
	)`,
	TABLE_VISITOR:`CREATE TABLE ${conf.mysql.tables.TABLE_VISITOR}
	(
		id INTEGER UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
		token VARCHAR(128) NOT NULL,
		timeVisited TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
		ua  VARCHAR(256)
	)`,
	CONSTRAINT_ARTICLE:`
		ALTER TABLE ${conf.mysql.tables.TABLE_ARTICLE} ADD CONSTRAINT fk_article_featuresID 
	FOREIGN KEY (featureID) REFERENCES ${conf.mysql.tables.TABLE_FEATURE}(id)
	ON UPDATE CASCADE ON DELETE CASCADE;`,
	TRIGGER_FEATURE_ADD:`
		CREATE TRIGGER tb_article_insert
	AFTER INSERT ON ${conf.mysql.tables.TABLE_ARTICLE}
	FOR EACH ROW 
	UPDATE ${conf.mysql.tables.TABLE_FEATURE} SET countArticle = countArticle+1 WHERE id = NEW.featureID;
	`,
	TRIGGER_FEATURE_DEL:`
		CREATE TRIGGER tb_article_delete
	AFTER DELETE ON ${conf.mysql.tables.TABLE_ARTICLE}
	FOR EACH ROW 
	UPDATE ${conf.mysql.tables.TABLE_FEATURE} SET countArticle = countArticle-1 WHERE id = OLD.featureID;
	`
};
module.exports = modeles;